import pymongo
from config.configs import ALPHA_DB_HOST, ALPHA_DB_PORT, DESKTOP_PATH
from tools.funciton import spu_to_articleNumber, skuId_to_size, write_xlsx, read_xlsx_file
import os
from bson import ObjectId

alpha = pymongo.MongoClient(ALPHA_DB_HOST, ALPHA_DB_PORT).alpha

file1 = os.path.join(DESKTOP_PATH, '0033.xlsx')

merchant = read_xlsx_file(file1, 1)
product = read_xlsx_file(file1, 2)
tracking = read_xlsx_file(file1, 4)
size = read_xlsx_file(file1, 3)
unique = read_xlsx_file(file1, 6)
ids = read_xlsx_file(file1, 0)


def get_wrong_product():
    """获取goat product错误数据（发错货号等同步问题）"""
    datas = []
    pipeline = [
      {
        '$match':
          {
            'saleStatus': "pending",
            'trackingNumber': {
              '$exists': 1
            }
          }
      },
      {
        '$project':
          {
            'productTemplateId': 1,
            'size': 1,
            'id': {
              '$toString': "$_id"
            },
            'merchantCode': 1,
            'trackingNumber': 1,
            'uniqueCode': 1,
            'createdAt': 1
          }
      },
      {
        '$lookup':
          {
            'from': "appointments_goat",
            'localField': "trackingNumber",
            'foreignField': "trackingNumber",
            'as': "result"
          }
      },
      {
        '$unwind':
          {
            'path': "$result"
          }
      },
      {
        '$project':
          {
            'ids': "$result.products",
            'productTemplateId': 1,
            'size': 1,
            'id': 1,
            'merchantCode': 1,
            'trackingNumber': 1,
            'uniqueCode': 1,
            'createdAt': 1
          }
      },
      {
        '$addFields': {
          'isInArray': {
            '$in': ["$id", "$ids"]
          }
        }
      },
      {
        '$match': {
          'isInArray': 'false'
        }
      },
      {
        '$project': {
          'isInArray': 0,
          'ids': 0
        }
      }
    ]
    [datas.append([i['']]) for i in alpha.goatproducts.aggregate(pipeline)]



def stock_correct():
    for i in range(len(merchant)):
        alpha.goatproducts.update_one(
            {'merchantCode': {"$regex": str(merchant[i])}, 'productTemplateId': product[i],
             'trackingNumber': tracking[i], 'saleStatus': "wait_for_consigned"},
            {"$set": {'uniqueCode': str(unique[i]), 'size': size[i], 'saleStatus': "queued"}})


def delete_exceed_product():
    for i in range(len(ids)):
        alpha.goatproducts.delete_one({'_id': ObjectId(ids[i])})


# stock_correct()
# delete_exceed_product()


# stock_correct()
